package handler;


import java.io.File;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;


import com.example.banksys.QueryPageController;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/*
 * 生成访问记录Excel表格工具类
 */
public class arexcelUtil {
    private static WritableCellFormat wcf_value;            // 表格数据样式
    private static WritableCellFormat wcf_value_left;
    private static WritableCellFormat wcf_key;              // 表头样式
    private static WritableCellFormat wcf_name_left;        // 表名样式
    private static WritableCellFormat wcf_name_right;       // 表名样式
    private static WritableCellFormat wcf_name_center;      // 表名样式
    private static WritableCellFormat wcf_title;            // 页名称样式
    private static WritableCellFormat wcf_percent_float;

    private static String pathStr ;        //将导出Excel表格的路径设置为桌面

    // 生成Excel文件
    public void genarateExcel(ResultSet res,String path) throws Exception{
        pathStr=path+"/";
        /******   定义表格格式start    *****/
        WritableFont wf_key = new jxl.write.WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.BOLD);
        WritableFont wf_value = new jxl.write.WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD);
        //设置单元格样式
        wcf_value = new WritableCellFormat(wf_value);                               //单元格字体样式
        wcf_value.setAlignment(jxl.format.Alignment.CENTRE);                            //单元格水平对齐样式
        wcf_value.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);            //单元格垂直对齐样式
        wcf_value.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);     //单元格边框样式

        wcf_value_left = new WritableCellFormat(wf_value);
        wcf_value_left.setAlignment(jxl.format.Alignment.LEFT);
        wcf_value_left.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        wcf_value_left.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        wcf_value_left.setWrap(true);

        wcf_key = new WritableCellFormat(wf_key);
        wcf_key.setAlignment(jxl.format.Alignment.CENTRE);
        wcf_key.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

        wcf_name_left = new WritableCellFormat(wf_key);
        wcf_name_left.setAlignment(Alignment.LEFT);
        wcf_name_left.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

        wcf_name_right = new WritableCellFormat(wf_key);
        wcf_name_right.setAlignment(Alignment.LEFT);

        wcf_name_center = new WritableCellFormat(wf_key);
        wcf_name_center.setAlignment(Alignment.CENTRE);

        jxl.write.NumberFormat wf_percent_float = new jxl.write.NumberFormat("0.00");   //定义单元浮点数据类型
        wcf_percent_float = new jxl.write.WritableCellFormat(wf_value,wf_percent_float);
        wcf_percent_float.setAlignment(jxl.format.Alignment.CENTRE);
        wcf_percent_float.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        wcf_percent_float.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

        WritableFont wf_title = new jxl.write.WritableFont(WritableFont.createFont("微软雅黑"), 24, WritableFont.NO_BOLD);      //定义标题样式
        wcf_title = new WritableCellFormat(wf_title);
        wcf_title.setAlignment(Alignment.CENTRE);
        wcf_title.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        /******   定义表格格式end    *****/

        //在指定的路径生成空白的xls文件
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
        System.out.println();
        String filename = pathStr +"Account"+QueryPageController.acc_ID +"  "+df.format(new Date()) + ".xls";
        File file = new File(filename);
        file.createNewFile();

        //根据传输过来的list，定义excel表格的列数
        //int maxRow = list.size();
        //int maxCol = 10;


        try{
            WritableWorkbook wb = Workbook.createWorkbook(file);
            //设置Excel工作簿名称
            WritableSheet ws = wb.createSheet("账单", 0);
            int startRowNum = 0;    // 起始行
            int startColNum = 0;    // 起始列
            int maxColSize = 6;     // 最大列数
            // 设置列宽
            ws.setColumnView(0, 12);
            ws.setColumnView(1, 18);
            ws.setColumnView(2, 20);
            ws.setColumnView(3, 28);
            ws.setColumnView(4, 12);
            ws.setColumnView(5, 10);


            ws.addCell(new Label(startColNum, startRowNum, "账单", wcf_title));
            ws.mergeCells(startColNum, startRowNum, startColNum + maxColSize- 1, startRowNum);  //合并单元格，合并(1,0)到(1,9)
            startRowNum++;
            //第1行，绘制表头
            ws.addCell(new Label(startColNum, startRowNum, "交易编号",wcf_key));
            startColNum++;
            ws.addCell(new Label(startColNum, startRowNum, "交易种类", wcf_key));
            startColNum++;
            ws.addCell(new Label(startColNum, startRowNum, "交易类别", wcf_key));
            startColNum++;
            ws.addCell(new Label(startColNum, startRowNum, "交易时间", wcf_key));
            startColNum++;
            ws.addCell(new Label(startColNum, startRowNum, "交易金额", wcf_key));
            startColNum++;
            ws.addCell(new Label(startColNum, startRowNum, "交易柜员", wcf_key));
            //将行数加1，列数重置为0
            startRowNum++;
            startColNum = 0;

            //添加记录
            while(res.next()){

                ws.addCell(new Label(startColNum, startRowNum, ""+res.getLong("trade_ID") ,wcf_key));
                startColNum++;
                ws.addCell(new Label(startColNum, startRowNum, res.getString("type_name"), wcf_key));
                startColNum++;
                ws.addCell(new Label(startColNum, startRowNum, res.getString("type_category"), wcf_key));
                startColNum++;
                ws.addCell(new Label(startColNum, startRowNum, res.getTimestamp("trade_date").toString(), wcf_key));
                startColNum++;
                ws.addCell(new Label(startColNum, startRowNum, ""+res.getDouble("money"), wcf_key));
                startColNum++;
                ws.addCell(new Label(startColNum, startRowNum, ""+res.getLong("teller_ID"), wcf_key));
                //将行数加1，列数重置为0
                startRowNum++;
                startColNum = 0;
            }

            wb.write();     //生成Excel工作簿
            wb.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}
